﻿using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using System.Linq;
using System.Web;

namespace RestaurantWebService
{
    class DatabaseManager
    {
        private string server = "localhost";
        private string database = "RotanaRemoteDB";
        private string uid = "root";
        private string password = "";
        string connectionString;

        public DatabaseManager()
        {
            connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + "; Allow Zero Datetime=true;";
        }

        public DatabaseManager(String connectionString)
        {
            this.connectionString = connectionString;
        }

        public static void close(MySqlConnection conn)
        {
            try
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            catch (MySqlException e)
            {
                Console.WriteLine(e.Message);
            }
        }

        public static void close(MySqlDataReader reader)
        {
            try
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
            catch (MySqlException e)
            {
                Console.WriteLine(e.Message);
            }
        }

        /// <exception cref="System.Data.SqlClient.SqlException"></exception>
        public MySqlConnection getConnection()
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            conn.Open();
            return conn;
        }

        /// <exception cref="System.Data.SqlClient.SqlException"></exception>
        public int executeNonQuery(string sql)
        {
            MySqlConnection conn = getConnection();
            MySqlCommand command = new MySqlCommand(sql, conn);
            int rowsAffected = command.ExecuteNonQuery();
            command.Dispose();
            close(conn);
            return rowsAffected;
        }

        /// <exception cref="System.Data.SqlClient.SqlException"></exception>
        public object executeScalar(string sql)
        {
            MySqlConnection conn = getConnection();
            MySqlCommand command = new MySqlCommand(sql, conn);
            object value = command.ExecuteScalar();
            command.Dispose();
            close(conn);
            return value;
        }

        /// <exception cref="System.Data.SqlClient.SqlException"></exception>
        public MySqlDataReader getDataReader(string sql, MySqlConnection conn)
        {
            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();
            return reader;
        }
    }
}